package cn.jdy.plugins.service;

import java.beans.IntrospectionException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.SQLQuery;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import cn.jdy.common.constant.GlobalConstant;
import cn.jdy.framework.db.hibernate.BaseDao;
import cn.jdy.framework.util.StringUtil;
import cn.jdy.platform.entity.ConfValue;
import cn.jdy.platform.service.ConfService;
import cn.jdy.plugins.entity.Business;

/**
 * @author wuzhiliang
 * @title : 商机信息操作实现
 */
@Service
public class BusinessService extends BaseDao<Business> {
  @Autowired
  private ConfService confService;

  public Business queryById(String id) {
    return super.findOne("id", id, "status", "1");
  }

  /**
   * 查询商机信息
   * 
   * @return
   */
  public List<Business> query(Business entity) {
    DetachedCriteria dc = createFilter(entity, null, null);
    dc.setResultTransformer(DetachedCriteria.DISTINCT_ROOT_ENTITY);// 结果集去重
    return super.find(dc, entity.getiDisplayStart(), entity.getiDisplayLength() * 3);
  }

  /**
   * 查询商机信息
   * 
   * @return
   */
  public List<Business> query(Business entity, String fromDate, String toDate) {
    DetachedCriteria dc = createFilter(entity, fromDate, toDate);
    dc.setResultTransformer(DetachedCriteria.DISTINCT_ROOT_ENTITY);
    return super.find(dc, entity.getiDisplayStart(), entity.getiDisplayLength() * 3);
  }

  /**
   * 首页：查询审核通过的商机(包括审核通过，下架和已删除)
   * 
   * @return
   */
  public Long count(Business entity) {
    return super.count(createFilter(entity, null, null));
  }

  /**
   * 首页：查询审核通过的商机(包括审核通过，下架和已删除)
   * 
   * @return
   */
  public Long count(Business entity, String fromDate, String toDate) {
    return super.count(createFilter(entity, fromDate, toDate));
  }

  /**
   * 拼装条件
   * 
   * @param entity
   * @param page
   * @return
   */
  private DetachedCriteria createFilter(Business entity, String... date) {
    DetachedCriteria dc = DetachedCriteria.forClass(entityClass);

    if (StringUtil.isNotEmpty(entity.getKeywords())) {
      if (StringUtil.isNotEmpty(entity.getKeyType())) {
        dc.add(Restrictions.like(entity.getKeyType(), "%" + entity.getKeywords().trim() + "%"));
      }
    }
    if (StringUtil.isNotEmpty(entity.getStatus())) {
      dc.add(Restrictions.eq("status", entity.getStatus()));
    } else {
      dc.add(Restrictions.ne("status", "3"));
    }
    if (StringUtil.isNotEmpty(entity.getIsRecommend())) {
      dc.add(Restrictions.eq("isRecommend", entity.getIsRecommend()));
    }
    if (StringUtil.isNotEmpty(entity.getClassId())) {
      dc.add(Restrictions.eq("classId", entity.getClassId()));
    }
    if (StringUtil.isNotEmpty(entity.getTelephone())) {
      dc.add(Restrictions.eq("telephone", entity.getTelephone()));
    }
    if (StringUtil.isNotEmpty(entity.getProvince())) {
      dc.add(Restrictions.eq("province", entity.getProvince()));
    }
    if (StringUtil.isNotEmpty(entity.getCreater())) {
      dc.add(Restrictions.eq("creater", entity.getCreater()));
    }
    if (date.length > 0 && date[0] != null && date[1] != null && date[0].length() > 0
        && date[1].length() > 0) {
      dc.add(Restrictions.between("createTime", date[0], date[1]));
    }
    dc.addOrder(Order.desc("createTime"));
    return dc;
  }

  /**
   * @return flag
   * @title ： 批量更新商机信息
   * @description:
   */
  @Transactional
  public void update(char type, List<String> id) {
    switch (type) {
    case 'S':// 审核通过
      super.update(id.toArray(), "status", "1");
      break;
    case 'T':// 推荐审核通过
      super.update(id.toArray(), "isRecommend", "1");
      break;
    case 'N':// 推荐审核通过
      super.update(id.toArray(), "isRecommend", "0");
      break;
    case 'X':// 下架审核通过
      super.update(id.toArray(), "status", "2");
      break;
    case 'D':// 删除审核通过
      super.update(id.toArray(), "status", "3");
      break;
    default:
      break;
    }
  }

  /**
   * 查询商机信息
   * 
   * @return
   */
  public Map<String, Object> search(Business entity, Integer fromAcreage, Integer toAcreage,
      Integer fromInmoney, Integer toInmoney, Long[] pinleiId) throws IntrospectionException {
    Map<String, Object> map = new HashMap<String, Object>();
    // 准备拼接sql元素
    StringBuilder sqlFrom = new StringBuilder(
        " FROM business T LEFT JOIN member T2 ON t2.id = T.creater");
    StringBuilder sqlWhereOR = new StringBuilder();
    StringBuilder sqlWhereAnd = new StringBuilder();
    StringBuilder url = new StringBuilder();// URL参数，翻页使用
    String classId = entity.getClassId();
    String province = entity.getProvince();
    String city = entity.getCity();
    String county = entity.getCounty();
    String sSearch = entity.getsSearch();
    String keywords = entity.getKeywords();
    boolean indistinct = keywords != null && keywords.length() > 0;// 是否模糊搜索
    // 是否选中品类
    if (pinleiId != null && pinleiId.length > 0) {
      StringBuilder sbd = new StringBuilder();
      for (Long pinlei : pinleiId) {
        if (pinlei != null) {
          sbd.append(pinlei).append(',');
          url.append("&pinleiId=").append(pinlei);
        }
      }
      if (sbd.length() > 0) {
        sqlFrom.append(" LEFT JOIN business_pinlei T3 ON T3.businessId = T.id");
        sqlWhereAnd.append(" AND T3.pinleiid in (").append(sbd.deleteCharAt(sbd.length() - 1))
            .append(')');
      }
    }
    // 是否使用关键字模糊查询
    if (indistinct) {
      sqlWhereOR.append(" OR INSTR(T.username, '").append(keywords).append("')>0");
      sqlWhereOR.append(" OR INSTR(T.summary, '").append(keywords).append("')>0");
      url.append("&keywords=").append(keywords);
      List<ConfValue> bClass = confService.queryByType(GlobalConstant.TYPE_BUSINESS, null);
      for (ConfValue cv : bClass) {
        if (keywords.equals(cv.getValue0())) {
          classId = String.valueOf(cv.getId());
          entity.setClassId(classId);
          break;
        }
      }
    }
    // 判断过滤条件
    if (classId != null && !classId.isEmpty()) {
      url.append("&classId=").append(classId);
      sqlWhereAnd.append(" AND T.classId = '").append(classId).append("'");
    } else if (indistinct) {
      sqlWhereOR.append(" OR INSTR(T.classId, '").append(keywords).append("')>0");
    }
    if (province != null && !province.isEmpty()) {
      url.append("&province=").append(province);
      sqlWhereAnd.append(" AND T.province = '").append(province).append("'");
    } else if (indistinct) {
      sqlWhereOR.append(" OR INSTR(T.province, '").append(keywords).append("')>0");
    }
    if (city != null && !city.isEmpty()) {
      url.append("&city=").append(city);
      sqlWhereAnd.append(" AND T.city = '").append(city).append("'");
    } else if (indistinct) {
      sqlWhereOR.append(" OR INSTR(T.city, '").append(keywords).append("')>0");
    }
    if (county != null && !county.isEmpty()) {
      url.append("&county=").append(county);
      sqlWhereAnd.append(" AND T.county = '").append(county).append("'");
    } else if (indistinct) {
      sqlWhereOR.append(" OR INSTR(T.county, '").append(keywords).append("')>0");
    }
    // 面积范围
    if (fromAcreage != null && toAcreage != null) {
      sqlWhereAnd.append(" AND T.acreage BETWEEN ").append(fromAcreage).append(" AND ")
          .append(toAcreage);
      url.append("&fa=").append(fromAcreage).append("&ta=").append(toAcreage);
    } else if (fromAcreage != null) {
      sqlWhereAnd.append(" AND T.acreage > ").append(fromAcreage);
      url.append("&fa=").append(fromInmoney);
    } else if (toAcreage != null) {
      sqlWhereAnd.append(" AND T.acreage < ").append(toAcreage);
      url.append("&ta=").append(toAcreage);
    }
    // 投资金额范围
    if (fromInmoney != null && toInmoney != null) {
      sqlWhereAnd.append(" AND T.inmoney BETWEEN ").append(fromInmoney).append(" AND ")
          .append(toInmoney);
      url.append("&fi=").append(fromInmoney).append("&ti=").append(toInmoney);
    } else if (fromInmoney != null) {
      sqlWhereAnd.append(" AND T.inmoney > ").append(fromInmoney);
      url.append("&fi=").append(fromInmoney);
    } else if (toInmoney != null) {
      sqlWhereAnd.append(" AND T.inmoney < ").append(toInmoney);
      url.append("&ti=").append(toInmoney);
    }

    StringBuilder sql = new StringBuilder(
        "SELECT T.id,T.summary,T.userName,T.province,T.city,T.county,T.address,")
        .append(
            "T.telephone,T.classId,T.opeanDate,T.acreage,T.inmoney,T.price,T.stock,T.buyTimes,T.sort,T.jobprocess,")
        .append(
            "T.shopadress,T.agentsform,T.agentsGrade,T.agentDetails,T.status,T.isRecommend,T.remark,T.createTime,")
        .append("T.updateTime,T.creater,T.expiryDate,T.keyword").append(sqlFrom)
        .append(" WHERE T.status = '1'");
    StringBuilder sql1 = new StringBuilder("SELECT COUNT(1)").append(sqlFrom).append(
        " WHERE T.status = '1'");

    // sql拼接where条件
    if (sqlWhereOR.length() > 0) {
      sqlWhereOR.insert(0, '(').append(')').replace(2, 4, "");
      sql.append(" AND ").append(sqlWhereOR);
      sql1.append(" AND ").append(sqlWhereOR);
    }
    if (sqlWhereAnd.length() > 0) {
      sql.append(sqlWhereAnd);
      sql1.append(sqlWhereAnd);
    }
    // sql拼接排序条件
    if (sSearch != null && sSearch.length() > 0) {
      sql.append(" ORDER BY ").append(sSearch).append(entity.isAsc() ? " ASC" : " DESC");
      url.append("&sSearch=").append(sSearch).append("&asc=").append(entity.isAsc());
    } else {// 默认排序
      sql.append(" ORDER BY ").append("createTime").append(" DESC"); // 设置 默认降序
      url.append("&sSearch=").append("createTime").append("&asc=").append(entity.isAsc());
    }

    SQLQuery query = super.em().createSQLQuery(sql.toString());
    query.setFirstResult(entity.getiDisplayStart());
    query.setMaxResults(entity.getiDisplayLength());
    query.addEntity(Business.class);
    map.put("dataList", query.list());

    SQLQuery queryCount = super.em().createSQLQuery(sql1.toString());
    map.put("total", queryCount.uniqueResult());
    if (url.length() > 1)
      map.put("url", url.append("&").substring(1));
    return map;
  }

  /**
   * 查询商机信息
   * 
   * @return
   */
  public List searchM(Business entity, Integer fromAcreage, Integer toAcreage, Integer fromInmoney,
      Integer toInmoney) throws IntrospectionException {
    // 准备拼接sql元素
    StringBuilder sqlFrom = new StringBuilder(
        " FROM business T LEFT JOIN member T2 ON t2.id = T.creater");
    StringBuilder sqlWhereAnd = new StringBuilder();
    String classId = entity.getClassId();
    String province = entity.getProvince();
    String city = entity.getCity();
    String county = entity.getCounty();

    // 判断过滤条件
    if (classId != null && !classId.isEmpty()) {
      sqlWhereAnd.append(" AND T.classId = '").append(classId).append("'");
    }
    if (province != null && !province.isEmpty()) {
      sqlWhereAnd.append(" AND T.province = '").append(province).append("'");
    }
    if (city != null && !city.isEmpty()) {
      sqlWhereAnd.append(" AND T.city = '").append(city).append("'");
    }
    if (county != null && !county.isEmpty()) {
      sqlWhereAnd.append(" AND T.county = '").append(county).append("'");
    }
    // 面积范围
    if (fromAcreage != null && toAcreage != null) {
      sqlWhereAnd.append(" AND T.acreage BETWEEN ").append(fromAcreage).append(" AND ")
          .append(toAcreage);
    } else if (fromAcreage != null) {
      sqlWhereAnd.append(" AND T.acreage > ").append(fromAcreage);
    } else if (toAcreage != null) {
      sqlWhereAnd.append(" AND T.acreage < ").append(toAcreage);
    }
    // 投资金额范围
    if (fromInmoney != null && toInmoney != null) {
      sqlWhereAnd.append(" AND T.inmoney BETWEEN ").append(fromInmoney).append(" AND ")
          .append(toInmoney);
    } else if (fromInmoney != null) {
      sqlWhereAnd.append(" AND T.inmoney > ").append(fromInmoney);
    } else if (toInmoney != null) {
      sqlWhereAnd.append(" AND T.inmoney < ").append(toInmoney);
    }

    StringBuilder sql = new StringBuilder(
        "SELECT T.id,T.summary,T.userName,T.province,T.city,T.county,T.address,")
        .append(
            "T.telephone,T.classId,T.opeanDate,T.acreage,T.inmoney,T.price,T.stock,T.buyTimes,T.sort,T.jobprocess,")
        .append(
            "T.shopadress,T.agentsform,T.agentsGrade,T.agentDetails,T.status,T.isRecommend,T.remark,T.createTime,")
        .append("T.updateTime,T.creater,T.expiryDate,T.keyword").append(sqlFrom)
        .append(" WHERE T.status = '1'");
    StringBuilder sql1 = new StringBuilder("SELECT COUNT(1)").append(sqlFrom).append(
        " WHERE T.status = '1'");

    if (sqlWhereAnd.length() > 0) {
      sql.append(sqlWhereAnd);
      sql1.append(sqlWhereAnd);
    }

    if (StringUtil.isNotEmpty(entity.getSortType())) {
      sql.append(" ORDER BY ").append(entity.getSortType());
    } else {
      sql.append(" ORDER BY createTime DESC ");
    }

    sql.append(" limit :start,:length");

    SQLQuery query = super.em().createSQLQuery(sql.toString());
    query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
    query.setParameter("start", entity.getiDisplayStart());
    query.setParameter("length", entity.getiDisplayLength());
    return query.list();
  }

  public long searchTotal(Business entity, Integer fromAcreage, Integer toAcreage,
      Integer fromInmoney, Integer toInmoney) {
    // 准备拼接sql元素
    StringBuilder sqlFrom = new StringBuilder(
        " FROM business T LEFT JOIN member T2 ON t2.id = T.creater");
    StringBuilder sqlWhereAnd = new StringBuilder();
    String classId = entity.getClassId();
    String province = entity.getProvince();
    String city = entity.getCity();
    String county = entity.getCounty();

    // 判断过滤条件
    if (classId != null && !classId.isEmpty()) {
      sqlWhereAnd.append(" AND T.classId = '").append(classId).append("'");
    }
    if (province != null && !province.isEmpty()) {
      sqlWhereAnd.append(" AND T.province = '").append(province).append("'");
    }
    if (city != null && !city.isEmpty()) {
      sqlWhereAnd.append(" AND T.city = '").append(city).append("'");
    }
    if (county != null && !county.isEmpty()) {
      sqlWhereAnd.append(" AND T.county = '").append(county).append("'");
    }
    // 面积范围
    if (fromAcreage != null && toAcreage != null) {
      sqlWhereAnd.append(" AND T.acreage BETWEEN ").append(fromAcreage).append(" AND ")
          .append(toAcreage);
    } else if (fromAcreage != null) {
      sqlWhereAnd.append(" AND T.acreage > ").append(fromAcreage);
    } else if (toAcreage != null) {
      sqlWhereAnd.append(" AND T.acreage < ").append(toAcreage);
    }
    // 投资金额范围
    if (fromInmoney != null && toInmoney != null) {
      sqlWhereAnd.append(" AND T.inmoney BETWEEN ").append(fromInmoney).append(" AND ")
          .append(toInmoney);
    } else if (fromInmoney != null) {
      sqlWhereAnd.append(" AND T.inmoney > ").append(fromInmoney);
    } else if (toInmoney != null) {
      sqlWhereAnd.append(" AND T.inmoney < ").append(toInmoney);
    }
    StringBuilder sql1 = new StringBuilder("SELECT COUNT(1)").append(sqlFrom).append(
        " WHERE T.status = '1'");

    if (sqlWhereAnd.length() > 0) {
      sql1.append(sqlWhereAnd);
    }

    SQLQuery queryCount = super.em().createSQLQuery(sql1.toString());
    Object obj = queryCount.uniqueResult();
    if (obj != null) {
      return Long.parseLong(obj.toString());
    } else {
      return 0;
    }
  }

  /**
   * 最新商机排行榜查询
   * 
   * @return
   */
  public List<Business> queryTops(String topType, boolean isAsc) {
    DetachedCriteria dc = DetachedCriteria.forClass(entityClass);
    dc.addOrder(Order.asc(topType));
    dc.add(Restrictions.eq("status", "1"));
    return super.find(dc, 0, 10);
  }

  /**
   * 商机列表查询--wap
   * 
   * @param entity
   * @return
   */
  public List<HashMap> queryWapList(Business entity) {
    StringBuilder sql = new StringBuilder(
        "select id,summary,createTime from business where status = :status ");
    if (null != entity.getClassId() && !"".equals(entity.getClassId())) {
      sql.append(" and classId = :classId ");
    }
    if (StringUtil.isNotEmpty(entity.getSortType())) {
      sql.append(" ORDER BY ").append(entity.getSortType());
    } else {
      sql.append(" ORDER BY createTime DESC");
    }
    sql.append(" limit :start,:length");

    SQLQuery query = super.em().createSQLQuery(sql.toString());
    // 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
    query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
    if (null != entity.getClassId() && !"".equals(entity.getClassId())) {
      query.setParameter("classId", entity.getClassId());
    }
    query.setParameter("status", entity.getStatus());
    query.setParameter("start", entity.getiDisplayStart());
    query.setParameter("length", entity.getiDisplayLength());
    return query.list();
  }

  /**
   * 获取商机top数
   */
  public List<Map> getTop(int n) {
    String sql = "select id,summary,createTime from business where status = 1 order by createTime desc limit 0,?";
    SQLQuery query = super.em().createSQLQuery(sql);
    query.setParameter(0, n);
    // 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
    query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
    return query.list();
  }
}
